use mailserver;
GO
-- nhap du lieu bang Roles
insert Roles values('admin',1);
insert Roles values('staff',2);
insert Roles values('student',3);
GO
-- nhap du lieu bang country
insert Country values('Viet Nam',1);
insert Country values('France',2);
insert Country values('Japan',3);
GO
--nhap du lieu bang city
insert City values(1,'Ha Noi',1);
insert City values(1,'Ho Chi Minh',2);
insert City values(2,'abc',3);
insert City values(2,'klm',4);
insert City values(3,'some where',5);
GO
-- nhap du lieu account la staff
insert Accounts values(2,'anhdt@fpt.aptech.ac.vn','123456','tuan anh','doan',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'quandv@fpt.aptech.ac.vn','123456','quan','dang',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'khaint@fpt.aptech.ac.vn','123456','khai','nguyen',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'anhnt@fpt.aptech.ac.vn','123456','anh','nguyen',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
-- nhap du lieu account la student
insert Accounts values(3,'student1@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(3,'student2@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(3,'student3@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
GO
-- nhap du lieu bang category
insert Category values('Inbox',1,0);
insert Category values('Send',2,0);
insert Category values('Draft',3,0);
insert Category values('Trash',4,0);
GO
--------------------------------- THU TUC VOI MESSAGER ---------------------------------
-- thu tuc lay danh sach message da gui
create procedure sp_getSendMessage(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountTo_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId',SendMessagerType,Category_Id from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where AccountFrom_Id = @accountId and Category_Id = 2


GO
-- thu tuc lay danh sach message da nhan
create procedure sp_getMessageInbox(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountFrom_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId',SendMessagerType,Category_Id from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where AccountTo_Id = @accountId and Category_Id = 1

GO
--thu tuc xoa message
create procedure sp_removeMessage(@messageId int,@sendMessageType int)
as
begin
	delete from SendMessager where Messager_Id = @messageId and SendMessagerType = @sendMessageType;
	delete from Messager where Id = @messageId and Id not in(select Messager_Id from SendMessager)
end
GO
--thu tuc tim messge theo id
create procedure sp_findMessageById(@id int)
as
	select * from Messager where Id = @id;
GO
-- thu tuc cap nhap lai trang thai cua email sau lan dau duoc doc
create procedure sp_updateSendMessageStatus(@sendMessageId int,@status int)
as
	update SendMessager set SendMessagerStatus = @status where Id = @sendMessageId;
GO
-- thu tuc tra ve so email chua duoc doc cua tai khoan nguoi nhan
-- @numberEmail nhan ve so email chua duoc doc
create procedure sp_numberEmailNotRead(@accountToId int,@numberEmail int out)
as
select @numberEmail = COUNT(SendMessagerStatus) from SendMessager 
group by SendMessagerStatus,AccountTo_Id,SendMessagerType having AccountTo_Id = @accountToId and SendMessagerStatus = 1 and SendMessagerType = 2
/*
declare @number int;
execute sp_numberEmailNotRead 2,@number out
select @number
*/
GO
-- TAO HAM DE CAT CHOI
create function splitString(@text text, @delimitor nchar(1)) 
RETURNS
@table TABLE
(
    [Index] int Identity(1,1),
    [SplitText] varchar(200)
)
AS
 
BEGIN
    declare @current nvarchar(200)
    declare @endIndex int
    declare @textlength int
    declare @startIndex int
 
    set @startIndex = 1
 
    if(@text is not null)
    begin
        set @textLength = datalength(@text)
 
        while(1=1)
        begin
            set @endIndex = charindex(@delimitor, @text, @startIndex)
 
            if(@endIndex != 0)
            begin
                set @current = substring(@text,@startIndex, @endIndex - @StartIndex)
                Insert Into @table ([SplitText]) values(@current)
                set @startIndex = @endIndex + 1   
            end
            else
            begin
                set @current = substring(@text, @startIndex, datalength(@text)-@startIndex+1)
                Insert Into @table ([SplitText]) values(@current)
                break
            end
        end
 
    end
 
    return
END
GO
-- THU TUC GUI EMAIL
create procedure sp_sendMail
@subject nvarchar(100),
@attachFile nvarchar(100),
@content ntext,
@ip nvarchar(50),
@accountFromId int,
@listEmail ntext
as
	begin transaction
		-- khai bao
		declare @messageId int;
		declare @count int;
		declare @emailId nvarchar(200);
		declare @accountToId int;
		-- insert bao bang Messager		
		insert Messager(Subjects,AttachFile,Content,CreateDate,Ip)
		values(@subject,@attachFile,@content,GETDATE(),@ip);						
		-- lay ve message id vua insert
		set @messageId = (select top 1 Messager.Id from Messager order by Id desc);
		-- INSERT SENDMESSAGER
		-- insert email nguoi gui
		insert SendMessager(Messager_Id,Category_Id,AccountFrom_Id,AccountTo_Id,SendMessagerStatus,SendMessagerType)
		values(@messageId,2,@accountFromId,@accountFromId,1,1);
		-- insert email nguoi nhan
		set @count = (select COUNT(SplitText) from dbo.splitString(@listEmail,','));
		while(@count > 0)
		begin		
			set @emailId = (select SplitText from dbo.splitString(@listEmail,',') where [Index] = @count);
			set @accountToId = (select Id from Accounts where Email_Id = @emailId);
			insert SendMessager(Messager_Id,Category_Id,AccountFrom_Id,AccountTo_Id,SendMessagerStatus,SendMessagerType)
			values(@messageId,1,@accountFromId,@accountToId,1,2);		
			set @count = @count - 1;			
		end									
	commit transaction
GO
-- thu tuc cap nhap category email la trash
create procedure sp_updateToTrash
@sendMessageId int
as
update SendMessager set Category_Id = 4 where Id = @sendMessageId;
------------
GO
-- thu tuc lay danh sach trash theo account id
create procedure sp_showTrash(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountTo_Id,AccountFrom_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId',SendMessagerType,Category_Id from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where Category_Id = 4 and (AccountTo_Id = @accountId or AccountFrom_Id = @accountId)
GO
------------------------------- KET THUC THU TUC VOI MESSAGER ---------------------------------
----------------------------------------------------------------------------------------------------
-------------------------------- BAT DAU THU TUC VOI ACCOUNTS ---------------------------------

/*
-- thu tuc kiem tra dang nhap account
create procedure sp_checkAccount(@email nvarchar(100),@password nvarchar(100))
as
	select * from Accounts where Email_Id = @email and Passwords = @password;
*/
GO
-- THU TUC TIM ACCOUNT THEO ID
create procedure sp_findAccountById(@id int)
as
	select * from Accounts where Id = @id;
GO
-- THU TUC TRA VE DANH SACH CUA ACCOUNTS
create procedure sp_findAllAccounts
as
	select * from Accounts;
GO
-- THU TUC XOA MOT ACCOUNT
create procedure sp_removeAccounts(@accountId int)
as
	delete from Accounts where Id = @accountId;
GO
-- THU TUC THEM MOI VA CAP NHAP ACCOUNTS
create procedure sp_addUpdateAccounts
@accountId int,
@roleId int,
@emailId nvarchar(100),
@password nvarchar(32),
@firstName nvarchar(100),
@lastName nvarchar(100),
@gender bit,
@address nvarchar(500),
@phone nvarchar(100),
@countryId int,
@cityId int,
@isActive bit,
@isChangePass bit,
@firstAccess datetime,
@lastAccess datetime,
@createDate datetime,
@ip nvarchar(50),
@avartar nvarchar(100),
@courseId int
as
begin
	if(@accountId=0)
	insert Accounts values(@roleId,@emailId,@password,@firstName,@lastName,@gender,@address,@phone,@countryId,@cityId,@isActive,@isChangePass,@firstAccess,@lastAccess,@createDate,@ip,@avartar,@courseId);
	else
	update Accounts set Email_Id = @emailId,Passwords = @password,FirstName = @firstName,LastName = @lastName,
	Gender = @gender,Addresss = @address,Phone = @phone,Country_Id = @countryId,City_Id = @cityId,
	IsActive = @isActive,IsChangePass = @isChangePass,FirstAccess = @firstAccess,LastAccess = @lastAccess,
	CreateDate = @createDate,Ip = @ip,Avartar = @avartar,Course_Id = @courseId where Id = @accountId;
end
GO
-- THỦ TỤC LỌC ACCOUNT THEO COURSE_ID
create procedure sp_findAccountByCourseId
@courseId int
as
	select * from Accounts where Course_Id = @courseId
GO
-- THỦ TỤC LỌC ACCOUNT THEO EMAIL_ID
create procedure sp_findAccountByEmail(@email nvarchar(100))
as
	select * from Accounts where Email_Id = @email;
GO
-- THỦ TỤC LỌC ACCOUNT THEO EMAIL_ID VÀ COURSE_ID
create procedure sp_findAccountByEmailAndCourse
@courseId int,
@emailId nvarchar(100)
as
	select * from Accounts where Email_Id = @emailId and Course_Id = @courseId
GO

 -------------------------------------- KET THUC THU TUC VOI ACCOUNTS -------------------------
 --------------------------------------------------------------------------------------------
 ------------------------------ BAT DAU THU TUC VOI ROLES -------------------------------
 
 
 /*
 * THU TUC THEM MOI VA CAP NHAP ROLES
 */
 GO
create proc sp_AddUpdateRoles
@id int,
@roleName varchar(50),
@isDisplay int
as
if (@id = 0)
	insert into Roles(RoleName,IsDisplay) values(@roleName,@isDisplay)
else
	update Roles set RoleName=@roleName, IsDisplay=@isDisplay where Id=@id
GO
-- THU TUC LAY TAT CA ROLES
create proc getAllRoles
as
select * from Roles
GO
-- THU TUC LAY ROLES THEO ID
create proc sp_getRoleByID
@id int
as
select * from Roles where Id=@id
GO
-- THU TUC XOA ROLES THEO ID
create proc sp_deleteRole
@id int
as
delete from Roles where Id=@id
GO
------------------------------ KET THU VOI ROLES ---------------------------
------------------------------ BAT DAU VOI COUNTRY --------------------------
-- THU TUC THEM MOI VA UPDATE COUNTRY
create proc sp_AddUpdateCountry
@id int,
@countryName varchar(100),
@isDisplay int
as
begin
	if(@id = 0)
		insert into Country(CountryName,IsDisplay) values(@countryName,@isDisplay)
	else
		update Country set CountryName = @countryName,IsDisplay = @isDisplay where Id = @id
end
GO
-- THU TUC LAY TAT CA COUNTRY
create proc sp_getAllCountry
as
select * from Country
GO
-- THU TUC XOA COUNTRY THEO ID
create proc sp_deleteCountry
@id int
as
delete from Country where Id=@id
GO
-- thu tuc tim country theo id
create procedure sp_findCountryById
@id int
as
	select * from Country where Id = @id;
GO
------------------- KET THUC VOI COUNTRY ------------
------------- BAT DAU CITY -------
-- thu tuc lay tat ca thanh pho
create proc sp_getAllCity
as
select * from City	
GO
-- thu tuc xoa city theo id
create proc sp_deleteCity
@id int
as
delete from City where Id=@id
GO
-- thu tuc insert va update city
create procedure sp_AddUpdateCity
@id int,
@countryId int,
@cityName nvarchar(100),
@isDisplay int
as
	begin
		if(@id = 0)
			insert City(Country_Id,CityName,IsDisplay) values(@countryId,@cityName,@isDisplay);
		else
			update City set Country_Id = @countryId,CityName = @cityName,IsDisplay = @isDisplay where Id = @id
	end
GO
-- thu tuc tim city theo id
create procedure sp_findCityById
@id int
as
	select * from City where Id = @id
GO
------------------ KET THUC CITY -----------
----------------- BAT DAU VOI COURSE -------------
-- thuc thuc lay sanh sach course
Create proc sp_GetAllCourse
as
	select * from Course;
GO
-- thu tuc xoa course theo id
Create proc sp_DeleteCourse
@id int
as
	delete from Course where Id = @id;
GO
-- thu tuc insert va update course
create procedure sp_AddUpdateCourse
@id int,
@courseName nvarchar(100)
as
	begin
		if(@id = 0)
			insert Course(CourseName) values(@courseName);
		else
			update Course set CourseName = @courseName where Id = @id;
	end
GO
-- thu tuc tim course theo id
create procedure sp_findCourseById
@id int
as
	select * from Course where Id = @id;
GO
---------------- KET THUC COURSE---------------
--------------- BAT DAU CATEGORY--------------
-- thu tuc tra ve danh sach category
Create proc sp_getAllCategory
as
select * from Category
GO
-- thuc tuc xoa category theo id
Create proc sp_deleteCategoryByID
@id int
as 
	delete from Category where Id = @id
GO
-- thu tuc insert va update category
create procedure sp_AddUpdateCategory
@id int,
@categoryName nvarchar(100),
@isDisplay int,
@isSystem bit,
@isClass nvarchar(100)
as
	begin
		if(@id = 0)
			insert Category(CategoryName,IsDisplay,IsSystem,IsClass) values(@categoryName,@isDisplay,@isSystem,@isClass);
		else
			update Category set CategoryName = @categoryName,IsDisplay = @isDisplay,IsSystem = @isSystem,IsClass = @isClass where Id = @id;
	end
GO
-- thu tuc tim category theo id
create procedure sp_findCategoryById
@id int
as
	select * from Category where Id = @id
GO
----------------- KET THUC CATEGORY------------------
----------------- BAT DAU VOI NEWS ----------
-- thu tuc tra ve danh sach news
Create proc sp_getAllNews
as 
	select Id,Title,Descriptions,Images,CreateDate,IsActive,IsHot from News
go
-- thu tuc tim news theo id
Create proc sp_findNewsByID
@id int
as
	select * from News where Id = @id
go
-- xoa news theo id
Create proc sp_deleteNewsByID
@id int
as
	delete from News where Id = @id
go
-- thu tuc insert va update news
Create proc sp_insertUpdateNews
@id int,
@title nvarchar(4000),
@descriptions ntext,
@images nvarchar(1000),
@isActive bit,
@isHot int
as
begin
	if(@id=0)
		insert into News(Title,Descriptions,Images,CreateDate,IsActive,IsHot) values(@title,@descriptions,@images,GETDATE(),@isActive,@isHot)
	else
		update News set Title = @title, Descriptions = @descriptions, Images = @images, IsActive = @isActive, IsHot = @isHot
 where Id = @id
end
GO
------------------ KET THUC NEWS -------------------